package com.huatai.bi.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.huatai.bi.bo.DataSetColumnBO;
import com.huatai.bi.config.ExecutorConfig;
import com.huatai.bi.constant.DataDirectorConstant;
import com.huatai.bi.constant.RedisConstant;
import com.huatai.bi.constant.SystemConstant;
import com.huatai.bi.dto.bigScreen.*;
import com.huatai.bi.entity.*;
import com.huatai.bi.kafaka.SyncDataSetService;
import com.huatai.bi.mapper.BigScreenDataSetMapper;
import com.huatai.bi.mapper.BigScreenImageMapper;
import com.huatai.bi.mapper.DirectoryMapper;
import com.huatai.bi.service.*;
import com.huatai.bi.utils.FilterComponent.Node;
import com.huatai.bi.utils.FilterExp;
import com.huatai.bi.utils.LocalDateTimeUtils;
import com.huatai.bi.vo.ColumnAxisVO;
import com.huatai.bi.vo.ColumnDataVO;
import com.huatai.bi.vo.bigScreenVO.*;
import com.huatai.common.error.exception.ServiceException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;

import java.text.NumberFormat;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * @BelongsProject: itc-airport-cloud
 * @BelongsPackage: com.itc.bi.service.impl
 * @Author: hecaiy
 * @CreateTime: 2024-07-11  16:26
 * @Description: TODO
 * @Version: 1.0
 */
@Service
@Slf4j
public class BigScreenDataSetServiceImpl extends ServiceImpl<BigScreenDataSetMapper,BigScreenDataSetEntity> implements BigScreenDataSetService {

    @Autowired
    private DirectoryMapper directoryMapper;
    @Autowired
    private DataSetColumnService dataSetColumnService;
    @Autowired
    private DataProcessService dataProcessService;
    @Autowired
    private BigScreenImageMapper bigScreenImageMapper;
    @Autowired
    private ClickHouseJDBCService clickHouseJDBCService;
    @Autowired
    private FunctionConfigService functionConfigService;
    @Autowired
    private StringRedisTemplate stringRedisTemplate;
    @Autowired
    @Lazy
    private BigScreenPageVersionService bigScreenPageVersionService;
    @Autowired
    SysDictDataService sysDictDataService;
	@Autowired
	SyncDataSetService syncDataSetService;

    @Override
    public List<ProDirDataSetVO> getBigScreenDataSetList(String parentCode){
        if (ObjectUtil.isNull(parentCode)){
            throw new ServiceException("目标态势编码不能为空");
        }
        BigScreenPageVersionEntity versionEntity = bigScreenPageVersionService.getOne(Wrappers.<BigScreenPageVersionEntity>lambdaQuery()
                .eq(BigScreenPageVersionEntity::getParentCode, parentCode));
                //20240829提出 下钻不受发布状态和授权的影响
//                .eq(BigScreenPageVersionEntity::getIsPublish, ScreenConstant.PUBLISH));
        if(versionEntity!=null){
            BigScreenDataSetEntity entity = this.getOne(Wrappers.<BigScreenDataSetEntity>lambdaQuery().eq(BigScreenDataSetEntity::getCode, versionEntity.getCode()));
            //当大屏未绑定数据集的时候直接返回空对象
            if(ObjectUtil.isNotEmpty(entity)){
                String dtIds = entity.getDtIds();
                if(StringUtils.isNotEmpty(dtIds)){
                    List<ProDirDataSetVO> proDirDataSetByIds = bigScreenImageMapper.getProDirDataSetByIds(dtIds);
                    return proDirDataSetByIds;
                }
            }
        }
        return new ArrayList<>();

    }

    /**
     * 获取下拉树
     * @return
     */
    public List<DataSetTreeVO> getDataSetTree() {
        List<DataSetTreeVO> dataSetTree = new ArrayList<>();
        List<DirectoryEntity> directoryEntityList = directoryMapper.selectList(Wrappers.<DirectoryEntity>lambdaQuery()
                .eq(DirectoryEntity::getIsDeleted,0)
                .orderByAsc(DirectoryEntity::getIsDir)
                .orderByAsc(DirectoryEntity::getCreateTime));
        List<DataProcessEntity> list = dataProcessService.list(Wrappers.<DataProcessEntity>lambdaQuery().eq(DataProcessEntity::getIsDeleted,0).orderByDesc(DataProcessEntity::getCreateTime));
        //聚合指标数据
        List<DataProcessEntity> aggregateList = list.stream().filter(p -> p.getType().equalsIgnoreCase("aggregate")).collect(Collectors.toList());
        //加工数据集
        List<DataProcessEntity> processList = list.stream().filter(p -> p.getType().equalsIgnoreCase("process")).collect(Collectors.toList());
        for (DirectoryEntity entity : directoryEntityList){
            DataSetTreeVO vo = new DataSetTreeVO();
            vo.setId(entity.getDtId());
            vo.setPid(entity.getPid());
            vo.setName(entity.getName());
            //目录
            List<DataSetTreeVO> children = new ArrayList<>();
            vo.setIsSelect(false);
            if(entity.getIsDir().equals(DataDirectorConstant.ISDATASET)){
                vo.setIsSelect(true);
                //加工数据集
                processList.stream().filter(p -> p.getDtId().equals(entity.getDtId())).forEach(p -> {
                    Long id = p.getId();
                    DataSetTreeVO childrenVO = new DataSetTreeVO();
                    childrenVO.setId(p.getId());
                    childrenVO.setPid(p.getDtId());
                    childrenVO.setName("加工-"+p.getName());
                    childrenVO.setIsSelect(true);
                    childrenVO.setIsProcess(true);
                    //加工下的数据指标
                    List<DataProcessEntity> collect = aggregateList.stream().filter(t -> t.getDtId().equals(id)).collect(Collectors.toList());
                    if (collect != null && collect.size() > 0) {
                        childrenVO.setChildren(setProcessAggregateList(collect, p.getDtId()));
                    }
                    children.add(childrenVO);
                });
                //聚合指标数据
                List<DataProcessEntity> collect = aggregateList.stream().filter(t -> t.getDtId().equals(entity.getDtId())).collect(Collectors.toList());
                if(collect!=null && collect.size()>0){
                    List<DataSetTreeVO> dataSetTreeVOS = setProcessAggregateList(collect,entity.getDtId());
                    children.addAll(dataSetTreeVOS);
                }
            }
            vo.setChildren(children);
            dataSetTree.add(vo);
        }
        return getDirectTreeNew(0L,dataSetTree);
    }

    /**
     * API查询监控目录下数据集,包含加工数据
     * @return
     */
    public DataSetTreeVO monitorTree() {
        SysDictDataEntity dict = sysDictDataService.getOne(Wrappers.<SysDictDataEntity>lambdaQuery().eq(SysDictDataEntity::getDictType, "monitor_dir_id"));
        if(null == dict){
            return null;
        }

        Long dirId = Long.parseLong(dict.getDictValue());

        List<DataSetTreeVO> dataSetTree = new ArrayList<>();
        List<DirectoryEntity> directoryEntityList = directoryMapper.selectList(Wrappers.<DirectoryEntity>lambdaQuery()
                .eq(DirectoryEntity::getIsDeleted,0)
                .orderByAsc(DirectoryEntity::getIsDir)
                .orderByAsc(DirectoryEntity::getCreateTime));
        List<DataProcessEntity> processList = dataProcessService.list(Wrappers.<DataProcessEntity>lambdaQuery().eq(DataProcessEntity::getIsDeleted,0)
                .eq(DataProcessEntity::getType,SystemConstant.PROCESS_TYPE_PROCESS).orderByDesc(DataProcessEntity::getCreateTime));
        for (DirectoryEntity entity : directoryEntityList){
            DataSetTreeVO vo = new DataSetTreeVO();
            vo.setId(entity.getDtId());
            vo.setPid(entity.getPid());
            vo.setName(entity.getName());
            //目录
            vo.setIsSelect(false);
            vo.setIsProcess(false);
            List<DataSetTreeVO> children = new ArrayList<>();
            if(entity.getIsDir().equals(DataDirectorConstant.ISDATASET)){
                vo.setIsSelect(true);
                //加工数据集
                processList.stream().filter(p -> p.getDtId().equals(entity.getDtId())).forEach(p -> {
                    DataSetTreeVO childrenVO = new DataSetTreeVO();
                    childrenVO.setId(p.getId());
                    childrenVO.setPid(p.getDtId());
                    childrenVO.setName("加工-"+p.getName());
                    childrenVO.setIsSelect(true);
                    childrenVO.setIsProcess(true);
                    children.add(childrenVO);
                });
            }
            vo.setChildren(children);
            dataSetTree.add(vo);
        }
        List<DataSetTreeVO> list = getDirectTreeNew(0L,dataSetTree);
        DataSetTreeVO dataSetTreeVO = list.stream().filter(item -> item.getId().equals(dirId)).findFirst().orElse(null);
        return dataSetTreeVO;
    }

    /**
     * 组装树结构
     * @param pid
     * @param dataSetTreeVO
     * @return
     */
    private List<DataSetTreeVO> getDirectTreeNew(Long pid, List<DataSetTreeVO> dataSetTreeVO){
        List<DataSetTreeVO> children = new ArrayList<>();
        for (DataSetTreeVO entity : dataSetTreeVO){
            if (Objects.equals(entity.getPid(), pid)){
                if(!entity.getIsSelect()){
                    entity.setChildren(getDirectTreeNew(entity.getId(),dataSetTreeVO));
                }
                children.add(entity);
            }
        }
        return children;
    }

    /**
     * 设置聚合指标数据集
     * @param aggregateList
     */
    private List<DataSetTreeVO> setProcessAggregateList(List<DataProcessEntity> aggregateList,Long pid){
        List<DataSetTreeVO> children = new ArrayList<>();
        aggregateList.forEach(p -> {
            DataSetTreeVO vo = new DataSetTreeVO();
            vo.setId(p.getId());
            vo.setPid(pid);
            vo.setName("指标-"+p.getName());
            vo.setIsSelect(false);
            vo.setChildren(new ArrayList<>());
            children.add(vo);
        });
        return children;
    }

    /**
     * 根据数据集id获取数据集列
     *
     * @param dtId
     */
    public ScreenDataSetColumnVO getColumnList(Long dtId) {
        ScreenDataSetColumnVO vo = new ScreenDataSetColumnVO();
        List<BigScreenDataSetColumnVO> dataItem = new ArrayList<>();
        List<BigScreenDataSetColumnVO> computer = new ArrayList<>();
        if (Objects.isNull(dtId)) {
            throw new ServiceException("数据集id不能为空");
        }
        List<DataSetColumnEntity> columnListByDsId = getColumnListByDsId(dtId);
        for (DataSetColumnEntity item : columnListByDsId){
            dataItem.add(new BigScreenDataSetColumnVO(item.getColumnName(), item.getColumnComment(), item.getColumnType(),false));
        }

        //寻找数据加工指标d
        List<DataSetColumnBO> list = dataProcessService.getDataSetColumnByAggregate(dtId);
        if(list!=null && list.size()>0){
            for (DataSetColumnBO item : list){
                dataItem.add(new BigScreenDataSetColumnVO(item.getColumnName(), item.getColumnComment(), item.getColumnType(),true));
            }
        }

        String key = RedisConstant.BIG_SCREEN_FUNCONFIG_DATA_SET_COLUMN_LIST + dtId;
        String columnNameType = stringRedisTemplate.opsForValue().get(key);
        if (ObjectUtil.isNotEmpty(columnNameType)) {
            computer = JSON.parseArray(columnNameType, BigScreenDataSetColumnVO.class);
        }else {
            List<functionConfigEntity> entityList = functionConfigService.list(Wrappers.<functionConfigEntity>lambdaQuery()
                    .eq(functionConfigEntity::getTypeLabel, "聚合函数").orderByAsc(functionConfigEntity::getSort));
            if (CollectionUtil.isNotEmpty(entityList)) {
                for (functionConfigEntity item : entityList){
                    computer.add(new BigScreenDataSetColumnVO(item.getFunctionExpression(), item.getName(),"",false));
                }
                //缓存保留2个小时
                stringRedisTemplate.opsForValue().set(key, JSON.toJSONString(computer),2, TimeUnit.HOURS);
            }
        }
        vo.setDataItem(dataItem);
        vo.setComputer(computer);
        return vo;
    }

    /**
     * 根据数据集id获取数据集列
     * @param dtId
     * @return
     */
    private List<DataSetColumnEntity> getColumnListByDsId(Long dtId) {
        List<DataSetColumnEntity> dataSetColumnEntities = dataSetColumnService.columnListById(dtId);
//        List<DataSetColumnEntity> dataItem = new ArrayList<>();
//        String keyColumn = RedisConstant.BIG_SCREEN_CUL_DATA_SET_COLUMN_LIST + dtId;
//        String keyColumnDataItem = stringRedisTemplate.opsForValue().get(keyColumn);
//        if (ObjectUtil.isNotEmpty(keyColumnDataItem)) {
//            dataItem = JSON.parseArray(keyColumnDataItem, DataSetColumnEntity.class);
//        }else {
//            List<DataSetColumnEntity> list = dataSetColumnService.list(Wrappers.<DataSetColumnEntity>lambdaQuery()
//                    .eq(DataSetColumnEntity::getDataSetId, dtId));
//            if (CollectionUtil.isNotEmpty(list)) {
//                for (DataSetColumnEntity item : list){
//                    item.setColumnType(ColumnTypeUtil.getColumnType(item.getColumnType()));
//                    dataItem.add(item);
//                }
//                //缓存保留2个小时
//                stringRedisTemplate.opsForValue().set(keyColumn, JSON.toJSONString(dataItem),2, TimeUnit.HOURS);
//            }
//        }
        return dataSetColumnEntities;
    }

    public JSONObject getScreenDataSetDataListCommon(BigScreenDataSetQueryDTO dto) {
        //切片 同时横坐标只有一个是不走聚合切片
        if(dto.getXAxis()!=null && dto.getXAxis().size()==1){
            return getScreenDataSetDataList(dto);
        }else if(dto.getIsAggregate()!=null && dto.getIsAggregate().equalsIgnoreCase("aggregate")){
            return getScreenDataSetDataListQP(dto);
        }else {
            return getScreenDataSetDataList(dto);
        }
    }

    /**
     * 获取大屏数据集数据
     * 1.组装查询字段
     * 2.组装排序字段
     * 3.组装聚合函数和分组字段
     * 4.组装数据集联动过滤条件
     * 5.组装数据集下钻过滤条件
     * 6.组装结果集过滤条件
     * 7.全局过滤组件参数配置
     *
     * 过滤先后依据
     *
     *
     * @param dto 前端组装的查询条件
     * @return
     */
    @Override
    public JSONObject getScreenDataSetDataList(BigScreenDataSetQueryDTO dto) {
        JSONObject retData = new JSONObject();
        LinkedHashMap<String,ColumnDataVO> columnDataJson = new LinkedHashMap();
        if(CollectionUtil.isEmpty(dto.getXAxis()) && CollectionUtil.isEmpty(dto.getYAxis())){
            retData.put("columnData",columnDataJson);
            retData.put("data",new ArrayList<>());
            return retData;
        }

        ProDirDataSetVO vo = bigScreenImageMapper.getProDirDataSetById(dto.getId());
		if (ObjectUtil.isEmpty(vo)) {
			throw new ServiceException("数据集不存在!!!");
		}
		String tableNameTemp = syncDataSetService.paramTempSynData(vo,dto.getGlobalFilter(),dto.getTitle());
        String sql = "select ${fileds} from ${tableName} ${where} ${group} ${order} ${limit}";
        //组装分组字段
        StringBuilder groupBy = new StringBuilder();
        //组装排序字段
        StringBuilder orderBy = new StringBuilder();
        //累加分组
        StringBuffer sumAddGroup = new StringBuffer();
        //查询字段
        List<ColumnAxisVO> sqlList = new ArrayList<>();
        //字段重复校验
        Map<String,Integer> columnNum = new HashMap<>();

        //查询指标
        List<DataSetColumnBO> dataSetColumnByAggregate = dataProcessService.getDataSetColumnByAggregate(dto.getId());

        List<DataSetColumnEntity> columnEntityList = getColumnListByDsId(dto.getId());
        Map<String, DataSetColumnEntity> columnNameToColumnMap = columnEntityList.stream().collect(Collectors.toMap(DataSetColumnEntity::getColumnName, Function.identity()));
        boolean flag = false;
        Map<String,Integer> numberFormat = new HashMap<>();
		String colorSet = null;
        if (ObjectUtil.isNotEmpty(vo)) {
            String tableName = vo.getTableName();
			if(StringUtils.isNotBlank(tableNameTemp)){
				tableName = tableNameTemp;
			}
            //TODO  后续利用线程池做操作
            List<BigScreenXAxisDTO> xAxis = dto.getXAxis();
            if (CollectionUtil.isNotEmpty(xAxis)) {
                //遍历查询字段和排序字段
                for (BigScreenXAxisDTO x : xAxis) {
                    String key = x.getKey();
                    //数据库字段,拼接票号
                    String keyStr = datasouceColumnStr(key);
                    if(!columnNameToColumnMap.containsKey(key)){
                        throw new ServiceException(String.format("组件 %s 配置的横向维度字段 %s 不存在",dto.getTitle(),x.getLabel()));
                    }
                    DataSetColumnEntity column = columnNameToColumnMap.get(key);
                    sqlList.add(new ColumnAxisVO(datasouceColumnStr(key),"x"));
                    columnDataJson.put(key,new ColumnDataVO(key,key,column.getColumnType(),x.getLabel(),true,false,false));
                    if(flag && StringUtils.isNotBlank(orderBy)){
                        orderBy.append(",");
                    }
					if(flag && StringUtils.isNotBlank(groupBy)){
						groupBy.append(",");
					}
                    flag = true;
                    groupBy.append(keyStr);
                    /*String orderKey = null == x.getUp()? x.getDown(): x.getUp();
                    if(StringUtils.isNotBlank(orderKey) && !columnNameToColumnMap.containsKey(orderKey)){
                        throw new ServiceException(String.format("组件 %s 配置的横向维度字段 %s 排序字段不存在",dto.getTitle(),x.getLabel()));
                    }*/
                    //判断是够存在排序字段
                    orderBy.append(x.orderBy());
                }
				if(StringUtils.isNotBlank(orderBy)){
					orderBy.insert(0," order by ");
				}

                if(groupBy.indexOf(",")>0){
                    sumAddGroup.append("partition by ").append(groupBy.substring(0,groupBy.lastIndexOf(",")));
                }
                groupBy.insert(0," group by ");
            }

			//颜色配置
			colorSet = dto.getColorSet();
			if (StringUtils.isNotBlank(colorSet)) {

				String key = colorSet;
				String ketStr = datasouceColumnStr(key);
				if(!columnNameToColumnMap.containsKey(key)){
					throw new ServiceException(String.format("组件: %s 颜色配置字段不存在",dto.getTitle()));
				}
				sqlList.add(new ColumnAxisVO(datasouceColumnStr(key),"c"));

				if(groupBy.length()>0){
					orderBy.append(",");
					groupBy.append(",");
				}else{
					orderBy.append(" order by ");
					groupBy.append(" group by ");
				}
				groupBy.append(ketStr);
				orderBy.append(ketStr);

			}



            List<BigScreenYAxisDTO> yAxis = dto.getYAxis();
            if (CollectionUtil.isNotEmpty(yAxis)) {
                //遍历聚合字段
                for (BigScreenYAxisDTO y : yAxis) {
                    //sqlList.add(y.getKey());
                    if (ObjectUtil.isNotEmpty(y.getComputer())) {
                        String computer = y.getComputer();
                        String key = y.getKey();
                        //组装保留小数位数
                        Integer scale = y.getScale();
                        String multiple = "";
                        if(y.getPercent()){
                            multiple = "*100";
                        }
                        //统计数量不需要转换
                        DataSetColumnBO dataSetColumnBO = dataSetColumnByAggregate.stream().filter(p -> p.getColumnName().equalsIgnoreCase(y.getKey())).findFirst().orElse(null);
                        if(ObjectUtil.isNotEmpty(dataSetColumnBO)) {
                            String aliasName = columnNameAlias(key,columnNum);
                            String aliasNameStr = datasouceColumnStr(aliasName);
                            String fullSql = dataSetColumnBO.getFullSql();
                            String title = "";
                            if(SystemConstant.SUM_ADD.equals(y.getSummationType())){
                                numberFormat.put(aliasName,scale);
                                title = "-累计";
                                sqlList.add(new ColumnAxisVO("sum(("+fullSql+")"+multiple+") over("+orderBy+" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "+aliasNameStr ,"y"));
                            }else if(SystemConstant.SUM_GROUP_ADD.equals(y.getSummationType())){
                                numberFormat.put(aliasName,scale);
                                title = "-组内累计";
                                sqlList.add(new ColumnAxisVO("sum(("+fullSql+")"+multiple+") over("+sumAddGroup+orderBy+" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "+aliasNameStr ,"y"));
                            }else{
                                sqlList.add(new ColumnAxisVO("round(("+fullSql+")"+multiple+","+scale+") as "+aliasNameStr ,"y"));
                            }
                            columnDataJson.put(key,new ColumnDataVO(key,aliasName,"number",dataSetColumnBO.getColumnComment()+title,false,true,y.getPercent()));
                        }else {
                            if(!columnNameToColumnMap.containsKey(key)){
                                throw new ServiceException(String.format("组件 %s 配置的纵向维度字段 %s 不存在",dto.getTitle(),y.getLabel()));
                            }

                            String keyStr = datasouceColumnStr(key);
                            String pinjieData=y.getKey()+y.getComputer();
                            //字段重复处理
                            pinjieData = columnNameAlias(pinjieData,columnNum);
                            String pinjieDataStr = datasouceColumnStr(pinjieData);

                            DataSetColumnEntity column = columnNameToColumnMap.get(key);
                            String title = "";
                            if(SystemConstant.SUM_ADD.equals(y.getSummationType())){
                                numberFormat.put(pinjieData,scale);
                                title = "-累计";
                                sqlList.add(new ColumnAxisVO("sum("+computer+"(toFloat64("+keyStr+"))"+multiple+") over("+orderBy+" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "+pinjieDataStr ,"y"));
                            }else if(SystemConstant.SUM_GROUP_ADD.equals(y.getSummationType())){
                                numberFormat.put(pinjieData,scale);
                                title = "-组内累计";
                                sqlList.add(new ColumnAxisVO("sum("+computer+"(toFloat64("+keyStr+"))"+multiple+") over("+sumAddGroup+orderBy+" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "+pinjieDataStr ,"y"));
                            }else if(computer.equalsIgnoreCase("count")){
                                sqlList.add(new ColumnAxisVO(y.getComputer()+"("+keyStr+")"+multiple+" as "+pinjieDataStr ,"y"));
                            }else {
                                //字符串类型转换
                                if(columnEntityList.stream().filter(p->p.getColumnName().equalsIgnoreCase(key) && p.getColumnType().equalsIgnoreCase("string")).count()>0){
                                    //无法转为数字的字符串 默认转为0
                                    sqlList.add(new ColumnAxisVO("round("+y.getComputer()+"(toFloat64OrZero("+keyStr+"))"+multiple+","+scale+") as "+pinjieDataStr ,"y"));
                                }else {
                                    sqlList.add(new ColumnAxisVO("round("+y.getComputer()+"(toFloat64("+keyStr+"))"+multiple+","+scale+") as "+pinjieDataStr ,"y"));
                                }
                            }
                            columnDataJson.put(pinjieData,new ColumnDataVO(key,pinjieData,column.getColumnType(),column.getColumnComment()+"("+y.getComputer()+")"+title,false,true,y.getPercent()));
                        }
                    }
                }
            }

            //人工去重一次
            sqlList = sqlList.stream().filter(p->ObjectUtil.isNotEmpty(p.getColumnName())).distinct().collect(Collectors.toList());
            //查询字段
            String fieldSql = sqlList.stream().map(p->p.getColumnName()).collect(Collectors.joining(","));
            if(fieldSql.endsWith(",")){
                fieldSql = fieldSql.substring(0, fieldSql.length() - 1);
            }
            String orderBySql = orderBy.toString() ;

            String groupBySql = "";
            //只是包含横向维度   取消分组
            String whereSql = whereInit(dto,null,columnNameToColumnMap);

            //组装分页条件
            String limit = "";
            if(ObjectUtil.isNotEmpty(dto.getPageIndex()) && ObjectUtil.isNotEmpty(dto.getPageSize())){
                String sqlTotalSize = buildSql(sql, fieldSql, tableName, whereSql, groupBySql, orderBySql, limit);
                retData.put("totalSize",clickHouseJDBCService.getTableSizeSql(sqlTotalSize));

                Integer pageIndex = dto.getPageIndex()<1?1:dto.getPageIndex();
                Integer pageSize = dto.getPageSize()<10?10:dto.getPageSize();
                limit = " limit "+(pageIndex-1)*pageSize+","+pageSize;

                if(!CollectionUtils.isEmpty(dto.getYAxis())){
                    groupBySql = groupBy.toString();
                }
            }else{
                groupBySql = groupBy.toString();
            }

//			if(StringUtils.isBlank(orderBySql)){
//				//使用默认时间字段排序
//				orderBySql = "order by resource_ts desc";
//			}

            //TODO 若后续查询效率低 考虑判断是否包含聚合函数  若不包含聚合函数  则不考虑使用 group by 字段
            sql = buildSql(sql, fieldSql, tableName, whereSql, groupBySql, orderBySql, limit);
        }
        log.info("sql:"+sql);
        //查询数据
        List<LinkedHashMap<String, Object>> dataList = clickHouseJDBCService.queryTableBySql(sql);
        dataFormat(dataList,numberFormat);
        retData.put("columnData",columnDataJson);

		if(StringUtils.isNotBlank(tableNameTemp)){//删除临时表
			try {
				clickHouseJDBCService.dropTable(tableNameTemp);
			}catch (Exception e){}
		}

		if(StringUtils.isBlank(colorSet)){
			retData.put("data",dataList);
		}else{
			//横坐标数据
			Map<String,JSONObject> xAxisList = new HashMap<>();
			Map<String,Integer> xindex = new HashMap<>();
			//纵坐标数据
			Map<String,List<Object>> ydata = new LinkedHashMap<>();
			flag = true;
			for (LinkedHashMap<String, Object> data : dataList){
				flag = true;
				String xkey = "";
				String colorLabel = (String)data.get(colorSet);//颜色标签
				for (Map.Entry<String, Object> entry : data.entrySet()) {
					String key = entry.getKey();
					if(flag){
						if(colorSet.equals(key)){
							flag = false;
						}else{
							xkey = xkey+entry.getValue();
							if(!xAxisList.containsKey(xkey)){
								JSONObject xAxisJson = new JSONObject();
								xAxisJson.put("value",entry.getValue());
								xAxisJson.put("originalKey",key);
								xAxisList.put(xkey,xAxisJson);
								xindex.put(xkey,xindex.size()+1);
							}
						}
						continue;
					}

					if(!ydata.containsKey(colorLabel)){
						ydata.put(colorLabel,new ArrayList<>());
					}
					int num  = xindex.get(xkey)-1;
					int size = ydata.get(colorLabel).size();

					for(int i = size; i<num;i++){
						ydata.get(colorLabel).add(null);
					}
					ydata.get(colorLabel).add(entry.getValue());
				}
			}
			JSONObject dataJson = new JSONObject();
			dataJson.put("xAxis",xAxisList.values());
			List<JSONObject> ylist = new ArrayList<>();
			for(Map.Entry<String, List<Object>> item : ydata.entrySet()){
				JSONObject data = new JSONObject();
				data.put("name",item.getKey());
				data.put("data",item.getValue());
				ylist.add(data);
			}
			retData.put("data",ylist);
		}
        return retData;
    }

    /**
     * 获取大屏数据集数据切片逻辑
     * 1.组装查询字段
     * 2.组装排序字段
     * 3.组装聚合函数和分组字段
     * 4.组装数据集联动过滤条件
     * 5.组装数据集下钻过滤条件
     * 6.组装结果集过滤条件
     * 7.全局过滤组件参数配置
     *
     * 过滤先后依据
     *
     *
     * @param dto 前端组装的查询条件
     * @return
     */
    public JSONObject getScreenDataSetDataListQP(BigScreenDataSetQueryDTO dto) {
        JSONObject retData = new JSONObject();
        LinkedHashMap<String,ColumnDataVO> columnDataJson = new LinkedHashMap();
        if(CollectionUtil.isEmpty(dto.getXAxis()) && CollectionUtil.isEmpty(dto.getYAxis())){
            retData.put("columnData",columnDataJson);
            retData.put("data",new ArrayList<>());
            return retData;
        }

        ProDirDataSetVO vo = bigScreenImageMapper.getProDirDataSetById(dto.getId());
        if (ObjectUtil.isEmpty(vo)) {
            throw new ServiceException("数据集不存在!!!");
        }
		String tableNameTemp = syncDataSetService.paramTempSynData(vo,dto.getGlobalFilter(),dto.getTitle());
        String sql = "select ${fileds} from ${tableName} ${where} ${group} ${order} ${limit}";
        //组装分组字段
        StringBuilder groupBy = new StringBuilder();
        StringBuffer sumAddGroup = new StringBuffer();
        //组装排序字段
        StringBuilder orderBy = new StringBuilder();
        //查询字段
        List<ColumnAxisVO> sqlList = new ArrayList<>();
        Map<String,Integer> columnNum = new HashMap<>();

        List<DataSetColumnEntity> columnEntityList = getColumnListByDsId(dto.getId());
        Map<String, DataSetColumnEntity> columnNameToColumnMap = columnEntityList.stream().collect(Collectors.toMap(DataSetColumnEntity::getColumnName, Function.identity()));

        String tableName = vo.getTableName();
        //TODO  后续利用线程池做操作
        List<BigScreenXAxisDTO> xAxis = dto.getXAxis();
        boolean flag = false;
        if (CollectionUtil.isNotEmpty(xAxis)) {
            //遍历查询字段和排序字段
            for (BigScreenXAxisDTO x : xAxis) {
                String key = x.getKey();
                String ketStr = datasouceColumnStr(key);
                if(!columnNameToColumnMap.containsKey(key)){
					throw new ServiceException(String.format("组件: %s 数据集: %s 字段 %s 不存在",dto.getTitle(),vo.getDataSetName(),x.getLabel()));
                }
                DataSetColumnEntity column = columnNameToColumnMap.get(key);
                sqlList.add(new ColumnAxisVO(datasouceColumnStr(key),"x"));
                columnDataJson.put(key,new ColumnDataVO(key,key,column.getColumnType(),x.getLabel(),true,false,false));

                if(flag){
                    orderBy.append(",");
                    groupBy.append(",");
                }
                flag = true;
                groupBy.append(ketStr);
                /*String orderKey = null == x.getUp()? x.getDown(): x.getUp();
                if(StringUtils.isNotBlank(orderKey) && !columnNameToColumnMap.containsKey(orderKey)){
                    throw new ServiceException(String.format("组件 %s 配置的横向维度字段 %s 排序字段不存在",dto.getTitle(),x.getLabel()));
                }*/
                orderBy.append(x.orderBy());
            }
            orderBy.insert(0," order by ");
            if(groupBy.indexOf(",")>0){
                sumAddGroup.append("partition by ").append(groupBy.substring(0,groupBy.lastIndexOf(",")));
            }
            groupBy.insert(0," group by ");
        }

		//颜色配置
		String colorSet = dto.getColorSet();
		if (StringUtils.isNotBlank(colorSet)) {

			String key = colorSet;
			String ketStr = datasouceColumnStr(key);
			if(!columnNameToColumnMap.containsKey(key)){
				throw new ServiceException(String.format("组件: %s 颜色配置字段不存在",dto.getTitle()));
			}
			DataSetColumnEntity column = columnNameToColumnMap.get(key);
			sqlList.add(new ColumnAxisVO(datasouceColumnStr(key),"c"));

			if(groupBy.length()>0){
				orderBy.append(",");
				groupBy.append(",");
			}else{
				orderBy.append(" order by ");
				groupBy.append(" group by ");
			}
			groupBy.append(ketStr);
			orderBy.append(ketStr);
		}

        Map<String,Integer> numberFormat = new HashMap<>();

        List<BigScreenYAxisDTO> yAxis = dto.getYAxis();
        //纵坐标数据项 后面拼接数据用
        List<JSONObject> yAxisListParam = new ArrayList<>();
        List<DataSetColumnBO> dataSetColumnByAggregate = dataProcessService.getDataSetColumnByAggregate(dto.getId());
        if (CollectionUtil.isNotEmpty(yAxis)) {
            //遍历聚合字段
            for (BigScreenYAxisDTO y : yAxis) {
                JSONObject jsonObject = new JSONObject();
                //sqlList.add(y.getKey());
                if (ObjectUtil.isNotEmpty(y.getComputer())) {
                    String computer = y.getComputer();
                    String key = y.getKey();
                    String keyStr = datasouceColumnStr(key);
                    String pinjieData=y.getKey()+y.getComputer();
                    pinjieData = columnNameAlias(pinjieData,columnNum);
                    String pinjieDataStr = datasouceColumnStr(pinjieData);
                    //组装保留小数位数
                    Integer scale = y.getScale();
                    String multiple = "";
                    if(y.getPercent()){
                        multiple = "*100";
                    }
                    //统计数量不需要转换
                    DataSetColumnBO dataSetColumnBO = dataSetColumnByAggregate.stream().filter(p -> p.getColumnName().equalsIgnoreCase(key)).findFirst().orElse(null);
                    if(ObjectUtil.isNotEmpty(dataSetColumnBO)){
                        String aliasName = columnNameAlias(key,columnNum);
                        String aliasNamestr = datasouceColumnStr(aliasName);
                        String fullSql = dataSetColumnBO.getFullSql();
                        String title = "";
                        if(SystemConstant.SUM_ADD.equals(y.getSummationType())){
                            numberFormat.put(aliasName,scale);
                            title = "-累计";
                            sqlList.add(new ColumnAxisVO("sum(("+fullSql+")"+multiple+") over("+orderBy+" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "+aliasNamestr ,"y"));
                        }else if(SystemConstant.SUM_GROUP_ADD.equals(y.getSummationType())){
                            numberFormat.put(aliasName,scale);
                            title = "-组内累计";
                            sqlList.add(new ColumnAxisVO("sum(("+fullSql+")"+multiple+") over("+sumAddGroup+orderBy+" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "+aliasNamestr,"y"));
                        }else{
                            sqlList.add(new ColumnAxisVO("round(("+fullSql+")"+multiple+","+scale+") as " + aliasNamestr ,"y"));
                        }
                        columnDataJson.put(aliasName,new ColumnDataVO(key,aliasName,"number",dataSetColumnBO.getColumnComment()+title,false,true,y.getPercent()));
                        jsonObject.put("key",key);
                        jsonObject.put("label",columnDataJson.get(aliasName).getLabel());
                    }else {
                        if(!columnNameToColumnMap.containsKey(key)){
                            throw new ServiceException(String.format("数据集: %s 字段 %s 不存在",vo.getDataSetName(),y.getLabel()));
                        }
                        DataSetColumnEntity column = columnNameToColumnMap.get(key);
                        String title = "";
                        if(SystemConstant.SUM_ADD.equals(y.getSummationType())){
                            numberFormat.put(pinjieData,scale);
                            title = "-累计";
                            sqlList.add(new ColumnAxisVO("sum("+computer+"(toFloat64("+keyStr+"))"+multiple+") over("+orderBy+" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "+pinjieDataStr ,"y"));
                        }else if(SystemConstant.SUM_GROUP_ADD.equals(y.getSummationType())){
                            numberFormat.put(pinjieData,scale);
                            title = "-组内累计";
                            sqlList.add(new ColumnAxisVO("sum("+computer+"(toFloat64("+keyStr+"))"+multiple+") over("+sumAddGroup+orderBy+" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "+pinjieDataStr ,"y"));
                        }else if(computer.equalsIgnoreCase("count")){
                            sqlList.add(new ColumnAxisVO(y.getComputer()+"("+keyStr+")"+multiple+" as "+pinjieDataStr ,"y"));
                        }else {
                            //字符串类型转换
                            if(columnEntityList.stream().filter(p->p.getColumnName().equalsIgnoreCase(keyStr) && p.getColumnType().equalsIgnoreCase("string")).count()>0){
                                //无法转为数字的字符串 默认转为0
                                sqlList.add(new ColumnAxisVO("round("+y.getComputer()+"(toFloat64OrZero("+keyStr+"))"+multiple+","+scale+") as "+pinjieDataStr ,"y"));
                            }else {
                                sqlList.add(new ColumnAxisVO("round("+y.getComputer()+"(toFloat64("+keyStr+"))"+multiple+","+scale+") as "+pinjieDataStr ,"y"));
                            }
                        }
                        columnDataJson.put(pinjieData,new ColumnDataVO(key,pinjieData,column.getColumnType(),y.getLabel()+"("+y.getComputer()+")"+title,false,true,y.getPercent()));
                        jsonObject.put("key",pinjieData);
                        jsonObject.put("label",columnDataJson.get(pinjieData).getLabel());
                    }
                    yAxisListParam.add(jsonObject);
                }
            }
        }

        //人工去重一次
        sqlList = sqlList.stream().filter(p->ObjectUtil.isNotEmpty(p.getColumnName())).distinct().collect(Collectors.toList());
        //查询字段
        String fieldSql = sqlList.stream().map(p->p.getColumnName()).collect(Collectors.joining(","));
        if(fieldSql.endsWith(",")){
            fieldSql = fieldSql.substring(0, fieldSql.length() - 1);
        }
        String orderBySql = orderBy.toString() ;

        String groupBySql = "";
        String where = whereInit(dto,null,columnNameToColumnMap);

        //组装分页条件
        String limit = "";
        if(ObjectUtil.isNotEmpty(dto.getPageIndex()) && ObjectUtil.isNotEmpty(dto.getPageSize())){
            String sqlTotalSize = buildSql(sql, fieldSql, tableName, where, groupBySql, orderBySql, limit);
            retData.put("totalSize",clickHouseJDBCService.getTableSizeSql(sqlTotalSize));

            Integer pageIndex = dto.getPageIndex()<1?1:dto.getPageIndex();
            Integer pageSize = dto.getPageSize()<10?10:dto.getPageSize();
            limit = " limit "+(pageIndex-1)*pageSize+","+pageSize;

            if(!CollectionUtils.isEmpty(dto.getYAxis())){
                groupBySql = groupBy.toString();
            }
        }else{
            groupBySql = groupBy.toString();
        }
		if(StringUtils.isBlank(orderBySql)){
			//使用默认时间字段排序
			orderBySql = "order by resource_ts desc";
		}
        //若后续查询效率低 考虑判断是否包含聚合函数  若不包含聚合函数  则不考虑使用 group by 字段
        sql = buildSql(sql, fieldSql, tableName, where, groupBySql, orderBySql, limit);
        log.info("sql:"+sql);
        //查询数据
        List<LinkedHashMap<String, Object>> dataList = clickHouseJDBCService.queryTableBySql(sql);
        dataFormat(dataList,numberFormat);
        retData.put("columnData",columnDataJson);
		if(StringUtils.isNotBlank(tableNameTemp)){//删除临时表
			try {
				clickHouseJDBCService.dropTable(tableNameTemp);
			}catch (Exception e){}
		}
        //组装切片树状数据
//        List<JSONObject> bigDataSetAggregateVOS = buildFCTree(dto.getXAxis(), dataList);
        retData.put("data",getDataSetDataList(dataList,yAxisListParam,colorSet));
        return retData;
    }

    private void dataFormat(List<LinkedHashMap<String, Object>> dataList,Map<String,Integer> formatMap){

        if(formatMap.size()==0){
            return;
        }
        NumberFormat format = NumberFormat.getInstance();
        for (LinkedHashMap<String, Object> data : dataList){
            for (Map.Entry<String,Integer> item : formatMap.entrySet()){
                Object value = data.get(item.getKey());
                if(Objects.nonNull(value)){
                    format.setMaximumFractionDigits(item.getValue());
                    data.put(item.getKey(),format.format(value));
                }
            }
        }
    }

    //字段加票号
    private String datasouceColumnStr(String column){
        return "`"+column+"`";
    }
    //获取数据项别名
    private String columnNameAlias(String columnName,Map<String,Integer> columnNum){
        String alias = columnName;
        if(columnNum.containsKey(columnName)){
            int num = columnNum.get(columnName);
            alias = alias + num;
            columnNum.put(columnName,num+1);
        }else{
            columnNum.put(columnName,1);
        }
        return alias;
    }

    /**
     *
     * 遍历数据封装
     * @param dataList
     * @return
     */
    public List<JSONObject> getDataSetDataList(List<LinkedHashMap<String, Object>> dataList,List<JSONObject> yAxisListParam,String colorSet) {
		List<JSONObject> dataListObjectParam = new ArrayList<>();
		if(StringUtils.isBlank(colorSet)){
			for (LinkedHashMap<String, Object> data : dataList){
				JSONObject dataJson = new JSONObject();
				//横坐标数据
				List<JSONObject> xAxisList = new ArrayList<>();
				//纵坐标数据
				List<JSONObject> yAxisList = new ArrayList<>();
				//判断是否是纵坐标的值
				for (Map.Entry<String, Object> entry : data.entrySet()) {
					String key = entry.getKey();
					JSONObject present = yAxisListParam.stream().filter(p->p.getString("key").equalsIgnoreCase(key)).findFirst().orElse(null);
					if(ObjectUtil.isNotEmpty(present)){
						JSONObject yAxisJson = new JSONObject();
						yAxisJson.put("value",entry.getValue());
						yAxisJson.put("originalKey",key);
						yAxisJson.put("label",present.getString("label"));
						yAxisList.add(yAxisJson);
					}else {
						JSONObject xAxisJson = new JSONObject();
						xAxisJson.put("value",entry.getValue());
						xAxisJson.put("originalKey",key);
						xAxisList.add(xAxisJson);
					}
				}
				dataJson.put("xAxis",xAxisList);
				dataJson.put("data",yAxisList);
				dataListObjectParam.add(dataJson);
			}
		}else{
			//横坐标数据
			Map<String,JSONObject> xAxisList = new HashMap<>();
			Map<String,Integer> xindex = new HashMap<>();
			//纵坐标数据
			Map<String,List<Object>> ydata = new LinkedHashMap<>();
			Boolean flag = true;
			for (LinkedHashMap<String, Object> data : dataList){
				flag = true;
				String xkey = "";
				String colorLabel = (String)data.get(colorSet);//颜色标签
				for (Map.Entry<String, Object> entry : data.entrySet()) {
					String key = entry.getKey();
					if(flag){
						if(colorSet.equals(key)){
							flag = false;
						}else{
							xkey = xkey+entry.getValue();
							if(!xAxisList.containsKey(xkey)){
								JSONObject xAxisJson = new JSONObject();
								xAxisJson.put("value",entry.getValue());
								xAxisJson.put("originalKey",key);
								xAxisList.put(xkey,xAxisJson);
								xindex.put(xkey,xindex.size()+1);
							}
						}
						continue;
					}

					JSONObject present = yAxisListParam.stream().filter(p->p.getString("key").equalsIgnoreCase(key)).findFirst().orElse(null);
					if(ObjectUtil.isNotEmpty(present)){
						if(!ydata.containsKey(colorLabel)){
							ydata.put(colorLabel,new ArrayList<>());
						}
						int num  = xindex.get(xkey)-1;
						int size = ydata.get(colorLabel).size();

						for(int i = size; i<num;i++){
							ydata.get(colorLabel).add(null);
						}
						ydata.get(colorLabel).add(entry.getValue());
					}
				}
			}
			JSONObject dataJson = new JSONObject();
			dataJson.put("xAxis",xAxisList.values());
			List<JSONObject> ylist = new ArrayList<>();
			for(Map.Entry<String, List<Object>> item : ydata.entrySet()){
				JSONObject data = new JSONObject();
				data.put("name",item.getKey());
				data.put("data",item.getValue());
				ylist.add(data);
			}
			dataJson.put("data",ylist);
			dataListObjectParam.add(dataJson);
		}
		return dataListObjectParam;
    }

    /**
     * 组装全局过滤条件
     * "htYearFilterate": "年份",
     * "htMonthFilterate": "年月",
     * "htDateFilterate": "日期",
     * "htMonthRangeFilterate": "年月区间",
     * "htDateRangeFilterate": "日期区间",
     * "htTextFilterate": "文本",
     * "htNumericalFilterate": "数值区间"
     * @param globalFilters
     * @return
     */
    public StringBuilder buildGlobalFilterSql(List<BigScreenGlobalFilterDTO> globalFilters,String title,Map<String, DataSetColumnEntity> columnNameToColumnMap) {
        if (CollectionUtil.isEmpty(globalFilters)) {
            return new StringBuilder();
        }
        StringBuilder globalWhere = new StringBuilder();
        String field = null;
        for (BigScreenGlobalFilterDTO globalFilter : globalFilters) {
            if(!columnNameToColumnMap.containsKey(globalFilter.getTargetField())){
                String label = null != globalFilter.getLabel()? globalFilter.getLabel(): globalFilter.getTargetField();
                throw new ServiceException(String.format("组件 %s 配置的全局过滤字段 %s 不存在",title,label));
            }

            field = "toDate(`"+globalFilter.getTargetField()+"`)";
            //传值才能组装条件df
            Object value = globalFilter.getValue();
            if(ObjectUtil.isNotEmpty(value)){
                //判断组件类型
                //年份 2024  判断逻辑  >=2024-01-01 <=2024-12-31
                if(globalFilter.getFieldType().equalsIgnoreCase("htYearFilterate")){
                    String startDate = value+"-01-01";
                    String endDate = value+"-12-31";
                    globalWhere.append(field + " >= '"+startDate+"' and ");
                    globalWhere.append(field + " <= '"+endDate+"' and ");
                }
                //年月  2024-05
                //判断逻辑  >=2024-05-01 <2024-06-01
                if(globalFilter.getFieldType().equalsIgnoreCase("htMonthFilterate")){
                    String[] split = value.toString().split("-");
                    String startDate = split[0]+"-"+split[1]+"-01";
                    String endDate = split[0]+"-"+(Integer.valueOf(split[1])+1)+"-01";
                    globalWhere.append(field + " >= '"+startDate+"' and ");
                    globalWhere.append(field + " < '"+endDate+"' and ");
                }
                //日期  2024-05-05
                if(globalFilter.getFieldType().equalsIgnoreCase("htDateFilterate")){
                    globalWhere.append(field + " = '"+value+"' and ");
                }
                //年月区间 ["2024-02","2024-02"]  ["","2024-06"] ["2024-06",""]
                // >=2024-02-01 <=2024-06-01
                if(globalFilter.getFieldType().equalsIgnoreCase("htMonthRangeFilterate")){
                    List<String> valueList = (ArrayList)value;
                    if(valueList.size()==2){
                        String valueParam1 = valueList.get(0);
                        //>= 2024-02-01
                        if(ObjectUtil.isNotEmpty(valueParam1)){
                            String[] splitParam1 = valueParam1.split("-");
                            String startDateParam1 = splitParam1[0]+"-"+splitParam1[1]+"-01";
                            globalWhere.append(field + " >= '"+startDateParam1+"' and ");

                        }
                        //< 2024-03-01
                        String valueParam2 = valueList.get(1);
                        if(ObjectUtil.isNotEmpty(valueParam2)){
                            String[] splitParam2 = valueParam2.split("-");
                            String endDateParam2 = splitParam2[0]+"-"+(Integer.valueOf(splitParam2[1])+1)+"-01";
                            globalWhere.append(field + " < '"+endDateParam2+"' and ");
                        }
                    }
                }
                //日期区间 ["2024-02-02","2024-02-02"]
                //toDate(`dateTime`) >= '2024-02-02' and toDate(`dateTime`) <= '2024-02-02' and
                if(globalFilter.getFieldType().equalsIgnoreCase("htDateRangeFilterate")){
                    List<String> valueList = (ArrayList)value;
                    if(valueList.size()==2){
                        if(ObjectUtil.isNotEmpty(valueList.get(0))){
                            globalWhere.append(field + " >= '"+valueList.get(0)+"' and ");
                        }
                        if(ObjectUtil.isNotEmpty(valueList.get(1))){
                            globalWhere.append(field + " <= '"+valueList.get(1)+"' and ");
                        }
                    }
                }
                //按钮组
                if(globalFilter.getFieldType().equalsIgnoreCase("htDateButtonGroupFilterate")){
                    String type = (String)value;
                    if(type.equals(SystemConstant.BIG_DATE_BUTTON_GROUP_CURRENT_DATE)){//当前
                        globalWhere.append(field + " = '"+ LocalDateTimeUtils.getCurrentDate()+"' and ");
                    }else if(type.equals(SystemConstant.BIG_DATE_BUTTON_GROUP_CURRENT_WEEK)){//本周
                        globalWhere.append(field + " >= '"+ LocalDateTimeUtils.weekFirstDate()+"' and ")
                                .append(field + " <= '"+ LocalDateTimeUtils.weekEndDate()+"' and ");
                    }else if(type.equals(SystemConstant.BIG_DATE_BUTTON_GROUP_CURRENT_MONTH)){//本月
                        globalWhere.append(field + " >= '"+ LocalDateTimeUtils.monthFirstDate()+"' and ")
                                .append(field + " <= '"+ LocalDateTimeUtils.monthEndDate()+"' and ");
                    }else if(type.equals(SystemConstant.BIG_DATE_BUTTON_GROUP_CURRENT_QUARTER)){//本季度
                        globalWhere.append(field + " >= '"+ LocalDateTimeUtils.quarterFirstDate()+"' and ")
                                .append(field + " <= '"+ LocalDateTimeUtils.quarterEndDate()+"' and ");
                    }else{//最近N天
                        try {
                            Integer days = Integer.parseInt(type);
                            globalWhere.append(field + " >= '"+ LocalDateTimeUtils.minusDays(days)+"' and ")
                                    .append(field + " <= '"+ LocalDateTimeUtils.getCurrentDate()+"' and ");
                        }catch (Exception e){
                            throw new ServiceException("参数异常");
                        }
                    }
                }

                //文本下拉 ["1","2","3"]
                //["1","2","3"]
                if(globalFilter.getFieldType().equalsIgnoreCase("htTextFilterate")){
                    List<String> valueList = (ArrayList)value;
                    if(CollectionUtil.isNotEmpty(valueList)){
                        String valueData = valueList.stream().map(p -> " '"+p+"' ").collect(Collectors.joining(","));
                        //去掉左后一个,
                        if(valueData.endsWith(",")){
                            valueData = valueData.substring(0, valueData.length() - 1);
                        }
                        globalWhere.append("`"+globalFilter.getTargetField() + "` in ("+valueData+") and ");
                    }
                }
                //数值区间 ["1","2"],["1",""],["","2"]
                //value >= 1 and value <= 2 and
                if(globalFilter.getFieldType().equalsIgnoreCase("htNumericalFilterate")){
                    List<Double> valueList = (ArrayList)value;
                    if(valueList.size()==2){
                        if(ObjectUtil.isNotEmpty(valueList.get(0))){
                            globalWhere.append("toFloat64(`"+globalFilter.getTargetField()+"`)" + " >= "+valueList.get(0)+" and ");
                        }
                        if(ObjectUtil.isNotEmpty(valueList.get(1))){
                            globalWhere.append("toFloat64(`"+globalFilter.getTargetField()+"`)" + " <= "+valueList.get(1)+" and");
                        }
                    }
                }
            }
        }
        deleteSBWithSpace(globalWhere, "a",4);
        return globalWhere;
    }

    /**
     * 组装sql
     * @param sql
     * @param fieldSql
     * @param tableName
     * @param whereSql
     * @param groupBySql
     * @param orderBySql
     * @param limit
     * @return
     */
    private String buildSql(String sql,String fieldSql,String tableName,String whereSql,String groupBySql,String orderBySql,String limit){
        return sql.replace("${fileds}", fieldSql)
                .replace("${tableName}", tableName)
                .replace("${where}", whereSql)
                .replace("${group}", groupBySql)
                .replace("${order}", orderBySql)
                .replace("${limit}", limit);
    }

    /**
     * 去除最后一个逗号
     *
     * @param sb
     */
    private void deleteSBWithSpace(StringBuilder sb, String replace,int length) {
        if (sb.length() > 0) {
            //删除字符串
            if(length>1){
                int index = sb.lastIndexOf(replace);
                if (index != -1) {
                    sb.delete(index, index + length);
                }
            }else {
                sb.deleteCharAt(sb.lastIndexOf(replace));
            }
        }
    }

    /**
     * 获取数据集
     *
     * @param type
     * @param name
     * @param pageIndex
     * @param pageSize
     * @return
     */
    public Page<ProDirDataSetVO> getDataSetPage(Integer type, String name, Integer pageIndex, Integer pageSize) {
        Page<ProDirDataSetVO> pageData = new Page<>();
        switch (type) {
            //全部数据集
            case 0:
                Page<ProDirDataSetVO> page = new Page<>(pageIndex < 1 ? 1 : pageIndex, pageSize < 1 ? 10 : pageSize);
                pageData = bigScreenImageMapper.getProDirDataSetPage(page, name);
                break;
            //原始数据集
            case 1:
                Page<ProDirDataSetVO> pageDir = new Page<>(pageIndex < 1 ? 1 : pageIndex, pageSize < 1 ? 10 : pageSize);
                pageData = directoryMapper.getProDirDataSetPage(pageDir, name);
                break;
            //加工数据集
            case 2:
                Page<DataProcessEntity> pagePro = new Page<>(pageIndex < 1 ? 1 : pageIndex, pageSize < 1 ? 10 : pageSize);
                Page<DataProcessEntity> pageDataPro = dataProcessService.page(pagePro, Wrappers.<DataProcessEntity>lambdaQuery().like(DataProcessEntity::getName, name));
                pageData.setPages(pageDataPro.getPages());
                pageData.setSize(pageDataPro.getSize());
                pageData.setCurrent(pageDataPro.getCurrent());
                pageData.setTotal(pageDataPro.getTotal());
                List<ProDirDataSetVO> dataList = new ArrayList<>();
                pageDataPro.getRecords().forEach(item -> {
                    dataList.add(new ProDirDataSetVO(item.getId(), item.getName(), item.getTableName(), item.getType()));
                });
                pageData.setRecords(dataList);
                break;
            default:
        }
        return pageData;
    }

    /**
     * 数据集类型接口
     *
     * @return
     */
    public List<HashMap<String, Object>> getTypeList() {
        List<HashMap<String, Object>> dataList = new ArrayList<>();
        dataList.add(new HashMap<String, Object>() {{
            put("type", 0);
            put("name", "全部数据集");
        }});
        dataList.add(new HashMap<String, Object>() {{
            put("type", 1);
            put("name", "原始数据集");
        }});
        dataList.add(new HashMap<String, Object>() {{
            put("type", 2);
            put("name", "加工数据集");
        }});
        return dataList;
    }

    /**
     * 根据数据集id获取获取数据详情
     *
     * @param id
     * @return
     */
    public ProDirDataSetVO getProDirDataSetById(Long id) {
        if (Objects.isNull(id)) {
            throw new ServiceException("数据集id不能为空");
        }
        return bigScreenImageMapper.getProDirDataSetById(id);
    }

    @Override
    public JSONObject crosstable(BigCrossTableQueryDTO dto) {

        JSONObject retData = new JSONObject();
        retData.put("previous",false);//行上一页
        retData.put("next",false);//行下一页
        retData.put("cloumnPrevious",false);//列上一页
        retData.put("columnNext",false);//列下一页
		retData.put("total",0);//总数

        ProDirDataSetVO vo = bigScreenImageMapper.getProDirDataSetById(dto.getId());
        if(null == vo){
            retData.put("data",new ArrayList<>());
            return retData;
        }

        List<DataSetColumnEntity> columnEntityList = getColumnListByDsId(dto.getId());
        Map<String, DataSetColumnEntity> columnNameToColumnMap = columnEntityList.stream().collect(Collectors.toMap(DataSetColumnEntity::getColumnName, Function.identity()));

        String tableName = vo.getTableName();

        columnAliasName(dto,columnNameToColumnMap);
        crossPageInit(dto);
        List<BigCrossTableDimensionDTO> rows = dto.getRows();
        List<BigCrossTableDimensionDTO> columns = dto.getColumns();
        List<BigCrossTableDimensionDTO> metrics = dto.getMetrics();

        retData.put("row",rows);//行字段
        retData.put("column",columns);//列字段
        retData.put("metric",metrics);//指标


        if(CollectionUtil.isEmpty(dto.getRows()) && CollectionUtil.isEmpty(dto.getColumns())
                && CollectionUtil.isEmpty(dto.getMetrics())){
            retData.put("data",new ArrayList<>());
            return retData;
        }

        StringBuilder field = new StringBuilder();//三维度字段
        StringBuilder nullField = new StringBuilder();//以行为维度列不存在
        StringBuilder columnField = new StringBuilder();//列维度汇总

        StringBuilder group = new StringBuilder();
        StringBuilder nullGroup = new StringBuilder();
        StringBuilder columnGroup = new StringBuilder();


        //组装排序字段
        StringBuilder order = new StringBuilder();

        //where条件
        StringBuilder where = new StringBuilder(whereInit(null,dto,columnNameToColumnMap));
        StringBuilder columnSumWhere = new StringBuilder(where);

        //聚合字段
        String metric = metricInit(dto,columnNameToColumnMap);

        //行分页
        if(CollectionUtil.isNotEmpty(dto.getRows())){

			//用行数据第一个字段分页
			BigCrossTableDimensionDTO first = rows.get(0);
			String firstColumnName = first.columnNameStr();
			int pageSize = dto.getPageSize();
			String pageSql = "select "+firstColumnName+" from "+tableName+where+" group by "+firstColumnName+" order by "+first.orderStr()+" limit "+((dto.getPageIndex()-1)*pageSize)+","+pageSize;
			String countSql = "select "+firstColumnName+" from "+tableName+where+" group by "+firstColumnName;
			Long count = clickHouseJDBCService.getTableSizeSql(countSql);
			retData.put("total",count);
			Long pages = (long) Math.ceil(count/10.0);

            retData.put("previous",true);
            retData.put("next",true);

            if(dto.getPageIndex() == 1){
                retData.put("previous",false);
            }else if(dto.getPageIndex() >= pages ){
                retData.put("next",false);
            }
            if(where.length() > 0 ){
                where.append(" and ");
            }else{
                where.append(" where ");
            }
            where.append(firstColumnName).append(" in (").append(pageSql).append(")");

            for (BigCrossTableDimensionDTO item : dto.getRows()){
                field.append(item.columnAliasNameStr()).append(",");
                nullField.append(item.columnAliasNameStr()).append(",");
                columnField.append("null,");

                group.append(item.columnNameStr()).append(",");
                nullGroup.append(item.columnNameStr()).append(",");

                order.append(item.orderStr()).append(",");
            }
            nullGroup.delete(nullGroup.length()-1,nullGroup.length());
            nullGroup.insert(0," group by ");
        }

        StringBuilder inWhere = new StringBuilder(where);
        StringBuilder notInWhere = new StringBuilder(where);
        //列分页
        if(CollectionUtil.isNotEmpty(dto.getColumns())){
            if(where.length() == 0 ){
                //inWhere.append(" where ");
                notInWhere.append(" where ");
            }else{
                //inWhere.append(" and ");
                notInWhere.append(" and ");
            }

            if(columnSumWhere.length() == 0 ){
                columnSumWhere.append(" where ");
            }else{
                columnSumWhere.append(" and ");
            }

            //用列数据第一个字段分页
            BigCrossTableDimensionDTO first = columns.get(0);
            String firstColumnName = first.columnNameStr();
            String pageSql = "select "+firstColumnName+" from "+tableName+where+" group by "+firstColumnName+" order by "+first.orderStr()+" limit "+((dto.getColumnPageIndex()-1)*10)+",10";
            String countSql = "select "+firstColumnName+" from "+tableName+where+" group by "+firstColumnName;
            Long count = clickHouseJDBCService.getTableSizeSql(countSql);
            Long pages = (long) Math.ceil(count/10.0);

            retData.put("cloumnPrevious",true);
            retData.put("columnNext",true);

            if(dto.getColumnPageIndex() == 1){
                retData.put("cloumnPrevious",false);
            }else if(dto.getColumnPageIndex() > pages ){
                retData.put("columnNext",false);
            }

            /*inWhere.append(firstColumnName).append(" in (")
                    .append(pageSql).append(")");*/
            notInWhere.append(firstColumnName).append(" not in (")
                    .append(pageSql).append(") and 1=2");

            columnSumWhere.append(firstColumnName).append(" in (")
                    .append(pageSql).append(")");

            for (BigCrossTableDimensionDTO item : dto.getColumns()){
                field.append(item.columnAliasNameStr()).append(",");
                nullField.append("null,");
                columnField.append(item.columnAliasNameStr()).append(",");

                group.append(item.columnNameStr()).append(",");
                columnGroup.append(item.columnNameStr()).append(",");

                order.append(item.orderStr()).append(",");
            }
        }

        field.append(metric);
        columnField.append(metric);

        if(CollectionUtil.isNotEmpty(dto.getMetrics())){
            for (int i = 0; i < dto.getMetrics().size(); i++) {
                nullField.append("null,");
            }
        }

        field.delete(field.length()-1,field.length());

        columnField.delete(columnField.length()-1,columnField.length());

        group.delete(group.length()-1,group.length());
        group.insert(0," group by ");

        order.delete(order.length()-1,order.length());
        order.insert(0," order by ");

        if(columnGroup.length() > 0 ){
            columnGroup.delete(columnGroup.length()-1,columnGroup.length());
            columnGroup.insert(0," group by ");
        }

        StringBuilder sql = new StringBuilder("select * from ( select ");
        sql.append(field).append(" from ")
                .append(tableName).append(inWhere).append(group).append(" with ROLLUP");
        if(columns.size()>0){
            nullField.delete(nullField.length()-1,nullField.length());
            sql.append(" union all select ").append(nullField)
                    .append(" from ").append(tableName).append(notInWhere).append(nullGroup);
        }

        if(metrics.size()>0 && (rows.size() > 0 || columns.size() > 0)){
            sql.append(" union all select ")
                    .append(columnField).append(" from ").append(tableName)
                    .append(columnSumWhere).append(columnGroup).append(" with ROLLUP");
        }

        sql.append(")").append(order);

        List<LinkedHashMap<String, Object>> dataList = new ArrayList<>();
        dataList.addAll(clickHouseJDBCService.queryTableBySql(sql.toString()));
        //移除值为null的key
        for(LinkedHashMap<String, Object> item : dataList){
            Iterator<Map.Entry<String, Object>> it = item.entrySet().iterator();
            while (it.hasNext()){
                Map.Entry<String, Object> next = it.next();
                if(ObjectUtils.isEmpty(next.getValue())){
                    it.remove();
                }
            }
        }

        retData.put("data",dataList);
        return retData;
    }
    private void crossPageInit(BigCrossTableQueryDTO dto){
        if(null == dto.getPageIndex() || dto.getPageIndex()<1){
            dto.setPageIndex(1);
        }

		if(null == dto.getPageSize() || dto.getPageSize()<1){
			dto.setPageSize(10);
		}

        if(null == dto.getColumnPageIndex() || dto.getColumnPageIndex()<1){
            dto.setColumnPageIndex(1);
        }
    }

    private void columnAliasName(BigCrossTableQueryDTO dto,Map<String, DataSetColumnEntity> columnNameToColumnMap){
        List<BigCrossTableDimensionDTO> rows = dto.getRows();
        List<BigCrossTableDimensionDTO> columns = dto.getColumns();
        List<BigCrossTableDimensionDTO> metrics = dto.getMetrics();
        Set<String> columnSet = new HashSet<>();
        Set<String> repeatSet = new HashSet<>();
        for(BigCrossTableDimensionDTO item : rows){
            if(!columnNameToColumnMap.containsKey(item.getColumnName())){
                throw new ServiceException(String.format("组件 %s 配置的横向维度字段 %s 不存在",dto.getTitle(),item.getLabel()));
            }
            if(columnSet.contains(item.getColumnName())){
                repeatSet.add(item.getColumnName());
            }else{
                columnSet.add(item.getColumnName());
            }
        }
        for(BigCrossTableDimensionDTO item : columns){
            if(!columnNameToColumnMap.containsKey(item.getColumnName())){
                throw new ServiceException(String.format("组件 %s 配置的纵向维度字段 %s 不存在",dto.getTitle(),item.getLabel()));
            }
            if(columnSet.contains(item.getColumnName())){
                repeatSet.add(item.getColumnName());
            }else{
                columnSet.add(item.getColumnName());
            }
        }
        for(BigCrossTableDimensionDTO item : metrics){
            if(columnSet.contains(item.getColumnName())){
                repeatSet.add(item.getColumnName());
            }else{
                columnSet.add(item.getColumnName());
            }
        }
        //字段重复处理
        rows.stream().forEach(item->item.setAliasColumnName(item.getColumnName()));
        columns.stream().forEach(item->item.setAliasColumnName(item.getColumnName()));
        metrics.stream().forEach(item->item.setAliasColumnName(item.getColumnName()));
        if(repeatSet.size()>0){
            int index = 0;
            for(String item : repeatSet){
                index = 0;
                for(BigCrossTableDimensionDTO dimension : rows){
                    if(item.equals(dimension.getColumnName())){
                        dimension.setAliasColumnName("row_"+item+index);
                        index++;
                    }
                }
                index = 0;
                for(BigCrossTableDimensionDTO dimension : columns){
                    if(item.equals(dimension.getColumnName())){
                        dimension.setAliasColumnName("column_"+item+index);
                        index++;
                    }
                }
                index = 0;
                for(BigCrossTableDimensionDTO dimension : metrics){
                    if(item.equals(dimension.getColumnName())){
                        dimension.setAliasColumnName("metric_"+item+index);
                        index++;
                    }
                }
            }
        }
        for(BigCrossTableDimensionDTO item : metrics){
            if(item.getColumnName().equals(item.getAliasColumnName())){
                item.setAliasColumnName(item.getColumnName()+0);
            }
        }
    }

    private String whereInit(BigScreenDataSetQueryDTO dto,BigCrossTableQueryDTO crossTableDto,Map<String, DataSetColumnEntity> columnNameToColumnMap){
        //过滤条件
        StringBuilder where = new StringBuilder();

        List<BigScreenGlobalFilterDTO> globalFilter;
        Node node;
        LinkageAndDowncastAllColumnDTO linkageAllColumn;
        LinkageAndDowncastAllColumnDTO downcastAllColumn;

        String title = null;
        if(null != dto){
            globalFilter = dto.getGlobalFilter();
            node = dto.getNode();
            linkageAllColumn = dto.getLinkageAllColumn();
            downcastAllColumn = dto.getDowncastAllColumn();
            title = dto.getTitle();
        }else{
            globalFilter = crossTableDto.getGlobalFilter();
            node = crossTableDto.getNode();
            linkageAllColumn = crossTableDto.getLinkageAllColumn();
            downcastAllColumn = crossTableDto.getDowncastAllColumn();
            title = crossTableDto.getTitle();
        }

        //TODO 全局过滤器 查询条件待确认
        where.append(buildGlobalFilterSql(globalFilter, title,columnNameToColumnMap));

        //遍历结果集过滤条件
        if(ObjectUtil.isNotEmpty(node)){
            FilterExp exp = new FilterExp(node,title,columnNameToColumnMap.keySet());
            String filter = exp.exp();
            if(where.length()>0 && filter.length() > 0){
                where.append(" and ");
            }
            where.append(filter);
        }

        linkageAndDowncast(where,linkageAllColumn,title,columnNameToColumnMap.keySet(),"联动");
        linkageAndDowncast(where,downcastAllColumn,title,columnNameToColumnMap.keySet(),"下钻");

        if(where.length()>0){
            where.insert(0," where ");
        }
        return where.toString();
    }

    //联动下钻
    private void linkageAndDowncast(StringBuilder where, LinkageAndDowncastAllColumnDTO dto,String title,Set<String> columns,String type){
        if (ObjectUtil.isNotEmpty(dto)) {
            LinkedHashMap<String,Object> sourceData = dto.getSourceData();
            ProDirDataSetVO vo = bigScreenImageMapper.getProDirDataSetById(dto.getSourceId());
            String tableName = vo.getTableName();

            List<DataSetColumnEntity> dataSetColumn = dataSetColumnService.columnListById(dto.getSourceId());
            Set<String> targetColumns = dataSetColumn.stream().map(item -> item.getColumnName()).collect(Collectors.toSet());



            //原数据拼接where条件
            StringBuilder linkageWhere = new StringBuilder();
            for (Map.Entry<String,Object> entry: sourceData.entrySet()){
                linkageWhere.append("`").append(entry.getKey()).append("` = '").append(entry.getValue()).append("' and ");
            }
            if(linkageWhere.length() > 0){
                linkageWhere.insert(0," where ");
                linkageWhere.delete(linkageWhere.lastIndexOf("and"),linkageWhere.length());
            }

            for(LinkageAndDowncastAllColumnDTO.Mapping item : dto.getMaps()){
                if(!targetColumns.contains(item.getSourceField())){
                    throw new ServiceException(String.format("组件 %s 配置的%s字段 %s 不存在",title,type,item.getLabel()));
                }
                for (Map.Entry<String,String> column : item.getTargetField().entrySet()){
                    if(!columns.contains(column.getKey())){
                        throw new ServiceException(String.format("组件 %s 配置的被%s字段 %s 不存在",title,type,column.getValue()));
                    }

                    if(where.length()>0){
                        where.append(" and ");
                    }
                    //联动数据项是组件使用数据项
                    if(sourceData.containsKey(item.getSourceField())){
                        where.append("`").append(column.getKey()).append("` = '")
                                .append(sourceData.get(item.getSourceField())).append("'");
                    }else{
                        where.append("`").append(column.getKey()).append("` in ( select DISTINCT ").append(item.getSourceField())
                                .append(" from ").append(tableName).append(linkageWhere).append(")");
                    }
                }
            }
        }
    }

    private String metricInit(BigCrossTableQueryDTO dto,Map<String, DataSetColumnEntity> columnNameToColumnMap){
        StringBuilder metricBuff = new StringBuilder();
        List<BigCrossTableDimensionDTO> metrics = dto.getMetrics();
        if (CollectionUtil.isNotEmpty(metrics)) {
            List<DataSetColumnBO> dataSetColumnByAggregate = dataProcessService.getDataSetColumnByAggregate(dto.getId());
            Map<String, String> aggregateMap = dataSetColumnByAggregate.stream().collect(Collectors.toMap(DataSetColumnBO::getColumnName, DataSetColumnBO::getFullSql));

            //遍历聚合字段
            for (BigCrossTableDimensionDTO metric : metrics) {
                Integer scale = metric.getScale();

                if(aggregateMap.containsKey(metric.getColumnName())){
                    metricBuff.append("round(" + aggregateMap.get(metric.getColumnName()) +","+scale + ") as `" + (metric.getAliasColumnName()) + "`,");
                }else if (ObjectUtil.isNotEmpty(metric.getComputer())) {
                    String computer = metric.getComputer();
                    String columnName = metric.getColumnName();
                    String columnNameStr = metric.columnNameStr();
                    String aliasColumnName = metric.getAliasColumnName();
                    String aliasColumnNameStr = "`"+aliasColumnName+"`";

                    if(!columnNameToColumnMap.containsKey(columnName)){
                        throw new ServiceException(String.format("组件 %s 配置的指标字段 %s 字段不存在",dto.getTitle(),metric.getLabel()));
                    }

                    //组装保留2位小数位数
                    //统计数量不需要转换
                    if (computer.equalsIgnoreCase("count")) {
                        metricBuff.append(metric.getComputer() + "(" + columnNameStr + ") as " + aliasColumnNameStr + ",");
                    } else {
                        //字符串类型转换
                        if (columnNameToColumnMap.get(columnName).getColumnType().equals(SystemConstant.DATA_TYPE_STRING)) {
                            //无法转为数字的字符串 默认转为0
                            metricBuff.append("round(" + metric.getComputer() + "(toFloat64OrZero(" + columnNameStr + ")),"+scale + ") as " + aliasColumnNameStr + ",");
                        } else {
                            metricBuff.append("round(" + metric.getComputer() + "(toFloat64(" + columnNameStr + ")),"+scale + ") as " + aliasColumnNameStr + ",");
                        }
                    }
                }
            }
        }
        return metricBuff.toString();
    }
}
